#!/usr/bin/env ruby

#------------------------------------------------------------------------------#
#                                                                              #
#  @Authors                                                                    #
#                                                                              #
#     - Almaraz Hernández, Jesús     (jesus.mah@gmail.com)                     #
#     - Campos Cantero, Pablo        (camposc.pablo@gmail.com)                 #
#     - Castelo Delgado, Tamara      (tcastelod@gmail.com)                     #
#                                                                              #
#  @Supervisor                                                                 #
#                                                                              #
#     - Santiago Montero, Rubén                                                #
#                                                                              #
#------------------------------------------------------------------------------#

################################################################################
# Required Libraries
################################################################################
require 'rubygems'
require 'sequel'
require 'json'

################################################################################
# Database configuration
################################################################################
DB = Sequel.sqlite('pruebaDB.db')

################################################################################
# Database
################################################################################

#------------------------------ Students --------------------------------------#
if (!DB.table_exists?(:students))
  DB.create_table :students do
      column :id, String, :primary_key=>true
      column :name, String
      column :surname, String
      column :password, String
    end
end

def newStudent(id, name, surname, password)
  begin
    DB[:students].insert(id, name, surname, password)
  rescue Exception
    raise(Exception, "Insert error in Database at student's table")
  end
end

def deleteStudent(idd)
  dss = DB[:students].filter(:id=>idd)
  #Comprobar que el numero de registros que ha borrado es 1. De no ser asi,
  #lanzamos una excepcion.
  if (dss.delete != 1)
    raise(Exception, "Delete error in Database at student's table")
  end
end

def modifyStudent(id1,password1)
  #Modifica el login y el password de un alumno con un id dado.
  # PODEMOS HACER OTRO TIPO DE MODIFICACIONES A PETICION

  DB[:students].filter(:id=>id1).update(:password=>password1)

end
#------------------------------- Teachers -------------------------------------#
if (!DB.table_exists?(:teachers))
  DB.create_table :teachers do
      column :id, String, :primary_key=>true
      column :name, String
      column :surname, String
      column :password, String
    end
end

def newTeacher(id, name, surname, password)
  begin
    DB[:teachers].insert(id, name, surname, password)
  rescue Exception
    raise(Exception, "Insert error in Databaseat teacher's table")
  end
end

def deleteTeacher(idd)
  dst = DB[:teachers].filter(:id=>idd)
  #Comprobar que el numero de registros que ha borrado es 1. De no ser asi,
  #lanzamos una excepcion.
  if (dst.delete != 1)
    raise(Exception, "Delete error in Database at teacher's table")
  end
end

def modifyTeacher(id1,password1)
  #Modifica el login y el password de un profesor con un id dado.
  # PODEMOS HACER OTRO TIPO DE MODIFICACIONES A PETICION

  DB[:teachers].filter(:id=>id1).update(:password=>password1)

end

#----------------------------- Administrators ---------------------------------#
if (!DB.table_exists?(:administrators))
  DB.create_table :administrators do
      String :id, :primary_key=>true
      String :name
      column :surname, String
      column :password, String
  end
  # Create an administrator
  DB[:administrators].insert(:id=>'00000000A',:name=>'administrator0',
    :surname=>'admsur0',:password=>'admpass0')
end

def newAdministrator(id, name, surname, password)
  begin
    DB[:administrators].insert(id, name, surname, password)
  rescue Exception
    raise(Exception, "Insert error in Database at administrator's table")
  end
end

def deleteAdministrator(idd)
  dsa = DB[:administrators].filter(:id=>idd)
  #Comprobar que el numero de registros que ha borrado es 1. De no ser asi,
  #lanzamos una excepcion.
  if (dsa.delete != 1)
    raise(Exception, "Delete error in Database at administrator's table")
  end
end

def modifyAdministrator(id1,password1)
  #Modifica el login y el password de un admin con un id dado.
  # PODEMOS HACER OTRO TIPO DE MODIFICACIONES A PETICION

  DB[:administrators].filter(:id=>id1).update(:password=>password1)

end

#-------------------------------- Labs ----------------------------------------#

if (!DB.table_exists?(:labs))
  DB.create_table :labs do
      primary_key(:id)
      column :template_id, String   #Foreign key. References to :id on :templates table
      column :teacher_id, String    #Foreign key. References to :id on :teachers table
      column :name, String
      column :group, String
      column :description, String
      
      
    end
end

def newLab(template_id, teacher_id, name, group, description)
  begin

  if DB[:teachers].filter(:id=>teacher_id).count == 1
      if DB[:templates].filter(:id=>template_id).count == 1
        DB[:labs].insert :template_id=>template_id, :teacher_id=>teacher_id,
          :name=>name, :group=>group, :description=>description
      else
        raise(Exception,'There are no template with id: ' + template_id)
      end
  else
     raise(Exception,'There are no teacher with id: ' + teacher_id)
  end
  rescue Exception => prueba
    raise(Exception, "Insert error in Database at lab's table. "+prueba)
  end
end

def deleteLab(idd)
  dst = DB[:labs].filter(:id=>idd)
  #Comprobar que el numero de registros que ha borrado es 1. De no ser asi,
  #lanzamos una excepcion.
  if (dst.delete != 1)
    raise(Exception, "Delete error in Database at lab's table")
  end
end

def modifyLab(id1,name1,description1)
  #Modifica el nombre y descripcion de un laboratorio con un id dado
  # PODEMOS HACER OTRO TIPO DE MODIFICACIONES A PETICION

  DB[:labs].filter(:id=>id1).update(:name=>name1, :description=>description1)

end


def modifyLab2(id1,templateid1)
  #Modifica el template de un laboratorio con un id dado
  # PODEMOS HACER OTRO TIPO DE MODIFICACIONES A PETICION

  DB[:labs].filter(:id=>id1).update(:template_id=>templateid1)

end


def modifyLab3(id1,teachername1,teacherid1)
  #Modifica el profesor (nombre e id propios) de un laboratorio con un id dado
  # PODEMOS HACER OTRO TIPO DE MODIFICACIONES A PETICION

  DB[:labs].filter(:id=>id1).update(:teacher_name=>teachername1, :teacher_id=>teacherid1)

end

#-------------------------------- Templates -----------------------------------#
if (!DB.table_exists?(:templates))
  DB.create_table :templates do
      column :name, String
      #URI template description
      column :description, String
      #URI template
      column :value, String
      primary_key(:id)
    end
end
def newTemplate(name, description, value)
  begin
    DB[:templates].insert :name=>name, :description=>description, :value=>value
  rescue Exception
    raise(Exception, "Insert error in Database at template's table")
  end
end

def deleteTemplate(idd)
  dst = DB[:templates].filter(:id=>idd)
  #Comprobar que el numero de registros que ha borrado es 1. De no ser asi,
  #lanzamos una excepcion.
  if (dst.delete != 1)
    raise(Exception, "Delete error in Database at template's table")
  end
end


def modifyTemplate(id1,name1,description1)
  #Modifica el nombre y descripcion de un template con un id dado
  # PODEMOS HACER OTRO TIPO DE MODIFICACIONES A PETICION

  DB[:templates].filter(:id=>id1).update(:name=>name1, :description=>description1)

end
#-------------------------------- Sessions ------------------------------------#
if (DB.table_exists?(:sessions))
  #At the begining, there are no sessions yet. So we drop the sessions table.
  DB.drop_table(:sessions)
end
DB.create_table :sessions do
    #Only students sessions avaible at this moments
    column :student_id, String  #Foreign key. References to :id on :students table
    column :labs_id, String     #Foreign key. References to :id on :labs table
    primary_key(:id)
  end

def newSession(student_id, labs_id)
  begin
    if DB[:students].filter(:id=>student_id).count == 1
      if DB[:labs].filter(:id=>labs_id).count == 1
        DB[:sessions].insert :student_id=>student_id, :labs_id=>labs_id
      else
        raise(Exception,'There are no lab with id: '+labs_id)
      end
    else
      raise(Exception,'There are no student with id: ' + student_id)
    end
  rescue Exception => prueba
    raise(Exception, "Insert error in Database at session's table"+prueba)
  end
end

def deleteSession(idd)
  dst = DB[:sessions].filter(:id=>idd)
  #Comprobar que el numero de registros que ha borrado es 1. De no ser asi,
  #lanzamos una excepcion.
  if (dst.delete != 1)
    raise(Exception, "Delete error in Database at sessions table")
  end
end

################################################################################
# Queries (return JSON)
################################################################################

def getStudents
  dsgs = DB[:students]
  if(dsgs.count > 0)
    return JSON.pretty_generate(dsgs.all)
  end
  return nil
end

def getTeachers
  dsgt = DB[:teachers]
  if(dsgt.count > 0)
    return JSON.pretty_generate(dsgt.all)
  end
  return nil
end

def getAdministrators
  dsga = DB[:administrators]
  if(dsga.count > 0)
    return JSON.pretty_generate(dsga.all)
  end
  return nil
end

def getLabs
  dsgl = DB[:labs]
  if(dsgl.count > 0)
    return JSON.pretty_generate(dsgl.all)
  end
  return nil
end

def getTemplates
  dsgtm = DB[:templates]
  if(dsgtm.count > 0)
    return JSON.pretty_generate(dsgtm.all)
  end
  return nil
end

def getSessions
  dsgss = DB[:sessions]
  if(dsgss.count > 0)
    return JSON.pretty_generate(dsgss.all)
  end
  return nil
end

=begin
  newStudent('00000000S','Jesus','Almaraz','Jesuspass')
  newStudent('11111111S','Pablo','Campos','Pablopass')
  newStudent('22222222S','Tamara','Castelo','Tamarapass')

  newTeacher('00000000T','Rafael','del Vado','Rafaelpass')
  newTeacher('11111111T','Alfredo','Bautista','Alfredopass')
  newTeacher('22222222T','Luis','Vazquez','Luispass')

  newAdministrator('11111111A','Administrator1','admsur1','admpass1')
  newAdministrator('22222222A','Administrator2','admsur2','admpass2')

  newTemplate('Template0','descripcion de la maquina','URI')
  newTemplate('Template1','descripcion de la maquina','URI')

  newLab(1,'11111111T','LSO','GroupA','Laboratorio de Sistemas Operativos')
  newLab(2,'22222222T','AN','GroupA','Laboratorio de Analisis numerico')

  newSession('00000000S',2)
  newSession('11111111S',1)



puts getStudents
puts getTeachers
puts getAdministrators
puts getTemplates
puts getLabs
puts getSessions
=end
